﻿using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Log.Log;
using System.Data.SQLite;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Math;
using System.Runtime.InteropServices;

namespace FixtureManageSystem.Models
{
    class LedgerService
    { 


        public async Task<Ledger> GetLedger()
        {
            List<Task<int>> ledgerTasks = new List<Task<int>> {
                   UpdateFixtureNum(),
                   GetBorrow(),
                   GetActualInventory(),
                   GetTotalBorrow(),
                   GetTotalReceipt(),
                   GetTotalReturn(),
                   GetReturn()
            };
           int[] results = await Task.WhenAll(ledgerTasks);
            return new Ledger()
            {
                Borrow = results[0],
                ActualInventory = results[1],
                TotalBorrow = results[2],
                TotalReceipt = results[3],
                TotalReturn = results[4],
                ReturnNum = results[5],
            };
        }
        
        /// <summary>
        /// 获取现有库存数量
        /// </summary>
        /// <returns></returns>
        static async Task<int> GetActualInventory()
        {
            string str = "select count(*) From Fixture;";
            new LogFactory().GetLog("logger").Info(true, str);
            SqliteHelper sh = new SqliteHelper();
            // 2、创建用于执行sql语句的对象
            SQLiteCommand SqlCom = sh.command(str);
            return await SqlCom.ExecuteScalarAsync().ContinueWith(t => DBNull.Value.Equals(t.Result) ? 0 : Convert.ToInt32(t.Result));
        }


        /// <summary>
        /// 入库总数
        /// </summary>
        /// <returns></returns>
        public static async Task<int> GetTotalReceipt()
        {
            string str = $"select count(*) from WareHouseIn ;";
            // 2、创建用于执行sql语句的对象
            new LogFactory().GetLog("logger").Info(true, str);
            SqliteHelper sh = new SqliteHelper();
            // 2、创建用于执行sql语句的对象
            SQLiteCommand SqlCom = sh.command(str);
            return await SqlCom.ExecuteScalarAsync().ContinueWith(t => DBNull.Value.Equals(t.Result) ? 0 : Convert.ToInt32(t.Result));
        }


        /// <summary>
        /// 本月借用总数
        /// </summary>
        /// <returns></returns>
        public static async Task<int> GetBorrow()
        {
            string str = $"select count(*) from ReturnFixture Where UpdateTime >= '{DateTime.Today.Year}-{DateTime.Today.Month}-01' ";
            str += " AND Status = '借用'; ";
            // 2、创建用于执行sql语句的对象
            new LogFactory().GetLog("logger").Info(true, str);
            SqliteHelper sh = new SqliteHelper();
            // 2、创建用于执行sql语句的对象
            SQLiteCommand SqlCom = sh.command(str);
            return await SqlCom.ExecuteScalarAsync().ContinueWith(t => DBNull.Value.Equals(t.Result) ? 0 : Convert.ToInt32(t.Result));
        }

        /// <summary>
        /// 本月归还总数
        /// </summary>
        /// <returns></returns>
        public static async Task<int> GetReturn()
        {
            string str = $"select count(*) from ReturnFixture Where UpdateTime >= '{DateTime.Today.Year}-{DateTime.Today.Month}-01' ";
            str += " AND Status = '归还'; ";
            // 2、创建用于执行sql语句的对象
            new LogFactory().GetLog("logger").Info(true, str);
            SqliteHelper sh = new SqliteHelper();
            // 2、创建用于执行sql语句的对象
            SQLiteCommand SqlCom = sh.command(str);
            return await SqlCom.ExecuteScalarAsync().ContinueWith(t => DBNull.Value.Equals(t.Result) ? 0 : Convert.ToInt32(t.Result));
        }


        /// <summary>
        /// 借用总数
        /// </summary>
        /// <returns></returns>
        public static async Task<int> GetTotalBorrow()
        {
            string str = $"select count(*) from ReturnFixture ";
            str += " Where Status = '借用'; ";
            // 2、创建用于执行sql语句的对象
            new LogFactory().GetLog("logger").Info(true, str);
            SqliteHelper sh = new SqliteHelper();
            // 2、创建用于执行sql语句的对象
            SQLiteCommand SqlCom = sh.command(str);
            return await SqlCom.ExecuteScalarAsync().ContinueWith(t => DBNull.Value.Equals(t.Result) ? 0 : Convert.ToInt32(t.Result));
        }

        /// <summary>
        /// 归还总数
        /// </summary>
        /// <returns></returns>
        public static async Task<int> GetTotalReturn()
        {
            string str = $"select count(*) from ReturnFixture ";
            str += " Where Status = '归还'; ";
            SqliteHelper sh = new SqliteHelper();
            // 2、创建用于执行sql语句的对象
            SQLiteCommand SqlCom = sh.command(str);
            return await SqlCom.ExecuteScalarAsync().ContinueWith(t => DBNull.Value.Equals(t.Result) ? 0 : Convert.ToInt32(t.Result));
        }


        /// <summary>
        /// 更新所有夹具的数量
        /// </summary>
        /// <returns></returns>
        public static async Task<int> UpdateFixtureNum()
        {
      
            SqliteHelper sqliteHelper = new SqliteHelper();
            await Task.Delay(1);
            try
            {
                string str = @"Select * from Fixture";
                SQLiteDataReader Sdr = sqliteHelper.read(str);
                List<string> barcodes = new List<string>();
                // 3、读取结果的数据
                while (Sdr.Read())
                {
                    barcodes.Add(Sdr["BarCode"].ToString());
                }

                foreach(string barcode in barcodes)
                {
                    str = "UPDATE fixture SET Num = " +
                        $"(SELECT COALESCE((SELECT SUM(WareHouseIn.Num) FROM WareHouseIn WHERE BarCode = '{barcode}'), 0) +" +
                        $" COALESCE((SELECT SUM(ReturnFixture.Actual_num) FROM ReturnFixture WHERE BarCode = '{barcode}'), 0))" +
                        $" WHERE BarCode = '{barcode}'  ";
                    sqliteHelper.Execute(str);
                }

            }
            catch (Exception ex)
            {
                return 0;
                // 处理或记录异常，可能需要抛出具体的异常信息给上层调用者。  
                throw new Exception("An error occurred while updating Fixture Num.", ex);
            }

            return 0;
        }


    }

}
